#include "sqlconnection.h"
#include <QtCore/QVariant>
#include "clientmanager.h"
#include <time.h>

SQLConnection::SQLConnection(ClientManager *clientManager, const QString &dbName, const QString &dbUser, const QString &dbPasswd, const QString &dbHost, int dbPort)
{
    m_clientManager = clientManager;

    m_database = QSqlDatabase::addDatabase("QMYSQL3");
    m_database.setDatabaseName(dbName);
    m_database.setUserName(dbUser);
    m_database.setPassword(dbPasswd);
    m_database.setHostName(dbHost);
    m_database.setPort(dbPort);

    if( !m_database.open() )
        m_console.addErrorMessage("Failed to open Database!");
}

SQLConnection::~SQLConnection()
{
    if( m_database.isOpen() )
        m_database.close();
}

bool SQLConnection::isRegistered(const QString &loginName)
{
    QSqlQuery sqlQuery( QString::null, m_database );
    sqlQuery.exec( "SELECT NULL FROM `users` WHERE `login`='" + loginName + "' ;" );

    if( sqlQuery.next() )
        return true;

    return false;
}

bool SQLConnection::isRegistered(const quint32 &uid)
{
    QSqlQuery sqlQuery( QString::null, m_database );

    QString query = QString("SELECT NULL FROM `users` WHERE `uid`='%d' ;").arg(uid);
    sqlQuery.exec( query );

    if( sqlQuery.next() )
        return true;

    return false;
}

// Returns 0 if there is no such account!
quint32 SQLConnection::getClientUid(const QString &loginName)
{
    QSqlQuery sqlQuery( QString::null, m_database );

    sqlQuery.exec( "SELECT `uid` FROM `users` WHERE `login`='" + loginName + "' ;" );

    if( sqlQuery.next() )
        return sqlQuery.value(0).toULongLong();

    return 0;
}

bool SQLConnection::checkLogin(const QString &loginName, const QString &passwdHash)
{
    QSqlQuery sqlQuery( QString::null, m_database );
    ClientData clientData;

    if( loginName == "" || passwdHash == "" )
        return false;

    fillClientData(&clientData, loginName);

    if( clientData.passwdHash == passwdHash )
        return true;

    return false;
}

void SQLConnection::fillClientData(ClientData *clientData, const QString &loginName)
{
    quint32 uid = getClientUid(loginName);

    if( uid > 0 )
        fillClientData(clientData, uid);
}

void SQLConnection::fillClientData(ClientData *clientData, const quint32 &uid)
{
    QSqlQuery sqlQuery( QString::null, m_database );

    // Request ClientData from database
    QString query = QString("SELECT `login`, `passwordHash`, `email`, `nickName` FROM `users` WHERE `uid`='%1' ;").arg(uid);
    sqlQuery.exec( query );

    if( !sqlQuery.next() )
        return;

    // fill ClientData
    clientData->uid        = uid;
    clientData->loginName  = sqlQuery.value(0).toString();
    clientData->passwdHash = sqlQuery.value(1).toString();
    clientData->email      = sqlQuery.value(2).toString();
    clientData->nickName   = sqlQuery.value(3).toString();
}

void SQLConnection::getFriendlist(std::vector<ClientFriend> *friendList, const QString &loginName)
{
    quint32 uid = getClientUid(loginName);

    if( uid > 0 )
        getFriendlist(friendList, uid);
}

void SQLConnection::getFriendlist(std::vector<ClientFriend> *friendList, const quint32 &uid)
{
    QSqlQuery sqlQuery( QString::null, m_database );

    // Request friendlist from database
    QString query = QString("SELECT `friends_uid`, `blocked` FROM `users_social` WHERE `uid`='%1' ;").arg(uid);
    sqlQuery.exec( query );

    while( sqlQuery.next() )
    {
        ClientData myFriend;
        fillClientData(&myFriend, sqlQuery.value(0).toULongLong());

        friendList->resize(friendList->size() + 1);
        friendList->at(friendList->size() - 1).uid       = myFriend.uid;
        friendList->at(friendList->size() - 1).loginName = myFriend.loginName;
        friendList->at(friendList->size() - 1).nickName  = myFriend.nickName;
        friendList->at(friendList->size() - 1).isOnline  = ( m_clientManager->getClient(myFriend.uid) ? true : false );
        friendList->at(friendList->size() - 1).blocked   = sqlQuery.value(1).toBool();
    }
}

bool SQLConnection::isFriendWith(const quint32 &uid, const quint32 &friend_uid)
{
    QSqlQuery sqlQuery( QString::null, m_database );

    QString query = QString("SELECT NULL FROM `users_social` WHERE `uid`='%1' AND `friends_uid`='%2' ;").arg(uid).arg(friend_uid);
    sqlQuery.exec( query );

    if( sqlQuery.next() )
        return true;

    return false;
}

bool SQLConnection::hasFriendrequest(const quint32 &uid, const quint32 &applicant_uid)
{
    QSqlQuery sqlQuery( QString::null, m_database );
    QString query = QString("SELECT NULL FROM `users_friendrequests` WHERE `uid`='%1' AND `requester_uid`='%2' ;").arg(uid).arg(applicant_uid);

    sqlQuery.exec( query );

    if( sqlQuery.next() )
        return true;

    return false;
}

void SQLConnection::addFriend(const quint32 &uid, const QString &friend_loginName, const QString &rMessage)
{
    QSqlQuery sqlQuery( QString::null, m_database );

    quint32 friend_uid = getClientUid(friend_loginName);

    if( friend_uid == 0 )
        return;
    if( isFriendWith(uid, friend_uid) )
        return;
    if( hasFriendrequest(uid, friend_uid) )
        return;
    if( friend_uid == uid )
        return;

    QString qrMessage = rMessage.length() < 1 ? "Please add me to your friendlist." : rMessage;

    // Insert request into request table
    QString query;
    query.sprintf( "INSERT INTO `users_friendrequests`(`uid`, `requester_uid`, `request_message`) "
                   "VALUES('%d', '%d', '%s') ;", friend_uid, uid, qrMessage.toStdString().c_str() );
    sqlQuery.exec( query );
}

void SQLConnection::getFriendrequests(std::vector<ClientFriendrequest> *friendrequests, const quint32 &uid)
{
    QSqlQuery sqlQuery( QString::null, m_database );

    // Request friendlist from database
    QString query = QString("SELECT `requester_uid`, `request_message` FROM `users_friendrequests` WHERE `uid`='%1' ;").arg(uid);
    sqlQuery.exec( query );

    while( sqlQuery.next() )
    {
        ClientData applicant;
        fillClientData(&applicant, sqlQuery.value(0).toULongLong());

        friendrequests->resize(friendrequests->size() + 1);
        friendrequests->at(friendrequests->size() - 1).applicant_uid       = sqlQuery.value(0).toULongLong();
        friendrequests->at(friendrequests->size() - 1).applicant_loginName = applicant.loginName;
        friendrequests->at(friendrequests->size() - 1).applicant_nickName  = applicant.nickName;
        friendrequests->at(friendrequests->size() - 1).rMessage            = sqlQuery.value(1).toString();
    }
}

void SQLConnection::getFriendrequests(std::vector<ClientFriendrequest> *friendrequests, const QString &loginName)
{
    quint32 uid = getClientUid(loginName);

    if( uid > 0 )
        getFriendrequests(friendrequests, uid);
}

void SQLConnection::acceptFriendrequest(const quint32 &uid, const quint32 &applicant_uid)
{
    QSqlQuery removeQuery( QString::null, m_database );
    QSqlQuery insertQuery( QString::null, m_database );

    if( !hasFriendrequest(uid, applicant_uid) )
        return;

    // Remove request from users_friendrequests table
    removeQuery.exec( QString("DELETE FROM `users_friendrequests` WHERE `uid`='%1' AND `requester_uid`='%2' ;").arg(uid).
                                                                                                                arg(applicant_uid) );

    if( isFriendWith(uid, applicant_uid) )
        return;

    // Insert request into users_social table
    insertQuery.exec( QString("INSERT INTO `users_social`(`uid`, `friends_uid`) VALUES('%1', '%2'),('%2', '%1') ;").arg(uid).
                                                                                                                    arg(applicant_uid) );
}

void SQLConnection::updateLastip(const quint32 &uid, const QString &ip)
{
    QSqlQuery sqlQuery( QString::null, m_database );
    QString query = QString("UPDATE `users` SET `lastip`='%1' WHERE `uid`='%2';").arg(ip).arg(uid);

    sqlQuery.exec( query );
}

void SQLConnection::updateLastseen(const quint32 &uid, const QString &timestamp)
{
    QSqlQuery sqlQuery( QString::null, m_database );
    QString query = QString("UPDATE `users` SET `lastseen`='%1' WHERE `uid`='%2';").arg(timestamp).
                                                                                    arg(uid);

    sqlQuery.exec( query );
}

void SQLConnection::saveOfflineMessage(const quint32 &uid, const quint32 &friend_uid, const QString &message)
{
    QSqlQuery sqlQuery( QString::null, m_database );
    QString query = QString("INSERT INTO `users_offlinemessages`(`to_uid`, `from_uid`, `timestamp`, `message`) "
                            "VALUES('%1', '%2', '%3', '%4') ;").arg(uid).
                                                                arg(friend_uid).
                                                                arg(std::time(NULL)).
                                                                arg(message);

    sqlQuery.exec( query );
}

int SQLConnection::getOfflineMessages(std::vector<ClientOfflineMessage> *offlineMessages, const quint32 &uid)
{
    int storedMessages = 0;
    QSqlQuery sqlQuery( QString::null, m_database );

    // Request friendlist from database
    QString query = QString("SELECT `from_uid`, `timestamp`, `message` FROM `users_offlinemessages` WHERE `to_uid`='%1' ;").arg(uid);
    sqlQuery.exec( query );

    while( sqlQuery.next() )
    {
        ClientData friendsData;
        fillClientData(&friendsData, sqlQuery.value(0).toULongLong());

        offlineMessages->resize(offlineMessages->size() + 1);
        offlineMessages->at(offlineMessages->size() - 1).friend_name = friendsData.nickName;
        offlineMessages->at(offlineMessages->size() - 1).timestamp   = sqlQuery.value(1).toULongLong();
        offlineMessages->at(offlineMessages->size() - 1).message     = sqlQuery.value(2).toString();

        ++storedMessages;

        // Remove the message from our database
        QSqlQuery sqlDeleteQuery( QString::null, m_database );
        QString deleteQuery = QString("DELETE FROM `users_offlinemessages` WHERE `to_uid`='%1' AND `from_uid`='%2' "
                                      "AND `timestamp`='%3' AND `message`='%4' ;").arg(uid).
                                                                                   arg(friendsData.uid).
                                                                                   arg(sqlQuery.value(1).toULongLong()).
                                                                                   arg(sqlQuery.value(2).toString());
        sqlDeleteQuery.exec( deleteQuery );
    }

    return storedMessages;
}
